package org.wzy.xmlg_backend.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.wzy.xmlg_backend.entity.Student;
import java.util.List;
import java.util.Map;

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    @Select("SELECT CASE gender WHEN 1 THEN '男' WHEN 2 THEN '女' END AS gender, COUNT(*) AS count FROM student GROUP BY gender")
    List<Map<String, Object>> countByGender();

    @Select("SELECT c.name AS className, COUNT(s.id) AS count " +
            "FROM student s JOIN class c ON s.class_id = c.id " +
            "GROUP BY c.name")
    List<Map<String, Object>> countByClass();

    @Select("SELECT CASE status WHEN 1 THEN '在读' WHEN 0 THEN '已离开' END AS status, COUNT(*) AS count FROM student GROUP BY status")
    List<Map<String, Object>> countByStatus();

    @Select("SELECT location, COUNT(*) AS count FROM student WHERE location IS NOT NULL AND location != '' GROUP BY location ORDER BY count DESC LIMIT 10")
    List<Map<String, Object>> countByLocation();
}